﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using Npgsql;
using InternRegistration.Model;
using System.Collections.ObjectModel;
using InternRegistration.ViewModel;
namespace InternRegistration.DataBase
{
    public class DBControl
    {
        //Singleton Approach! 
        //Only one instance is created when program launches.
        static DBControl instance = null;
        static readonly object padlock = new object();


       
        /// <summary>
        /// It's a thread-safe application of Singleton, using lock.
        /// </summary>
        public static DBControl Instance
        {
            get
            {
                lock (padlock)
                {
                    if (instance == null)
                    {
                        instance = new DBControl();
                    }
                    return instance;
                }
            }
        }


        //Parameters for queries.
        NpgsqlParameter IdParam;
        NpgsqlParameter NameParam;
        NpgsqlParameter SurnameParam;
        NpgsqlParameter BirthOfDayParam;
        NpgsqlParameter AddressParam;
        NpgsqlParameter EmailParam;
        NpgsqlParameter SchoolParam;
        NpgsqlParameter DepartmentParam;
        NpgsqlParameter GradeParam;
        NpgsqlParameter GPAParam;
        NpgsqlParameter SupervisorParam;
        NpgsqlParameter InternshipStartingParam;
        NpgsqlParameter InternshipEndingParam;
        NpgsqlParameter PicURLParam;
        NpgsqlParameter TelephoneNumberParam;
        NpgsqlParameter ProgrammingLanguagesParam;
        NpgsqlParameter CommentParam;
        NpgsqlParameter NewLanguageParam;

        public NpgsqlConnection dbConnection;

      
        #region ctor
        public DBControl() // Default Constructor of DBControl, which creates a connection and parameters that are going to used later.
        {
            //Making Connection
            dbConnection = new NpgsqlConnection("Server=localhost;Port=5432;User Id=postgres;Password=123456;Database=FirstDataBase;");
            try
            {
                //Opens Connection
                dbConnection.Open();

                //Instanciating the parameters
                IdParam = new NpgsqlParameter("@Id", NpgsqlTypes.NpgsqlDbType.Bigint);
                NameParam = new NpgsqlParameter("@Name", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
                SurnameParam = new NpgsqlParameter("@Surname", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
                BirthOfDayParam = new NpgsqlParameter("@BirthOfDay", NpgsqlTypes.NpgsqlDbType.Date);
                AddressParam = new NpgsqlParameter("@Address", NpgsqlTypes.NpgsqlDbType.Varchar, 200);
                EmailParam = new NpgsqlParameter("@Email", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
                SchoolParam = new NpgsqlParameter("@School", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
                DepartmentParam = new NpgsqlParameter("@Department", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
                GradeParam = new NpgsqlParameter("@Grade", NpgsqlTypes.NpgsqlDbType.Varchar, 20);
                GPAParam = new NpgsqlParameter("@GPA", NpgsqlTypes.NpgsqlDbType.Double);
                SupervisorParam = new NpgsqlParameter("@Supervisor", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
                InternshipStartingParam = new NpgsqlParameter("@InternshipStarting", NpgsqlTypes.NpgsqlDbType.Date);
                InternshipEndingParam = new NpgsqlParameter("@InternshipEnding", NpgsqlTypes.NpgsqlDbType.Date);
                PicURLParam = new NpgsqlParameter("@PicURL", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
                TelephoneNumberParam = new NpgsqlParameter("@TelephoneNumber", NpgsqlTypes.NpgsqlDbType.Varchar, 11);
                ProgrammingLanguagesParam = new NpgsqlParameter("@ProgrammingLanguages", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
                CommentParam = new NpgsqlParameter("@Comment", NpgsqlTypes.NpgsqlDbType.Varchar, 1000);
                NewLanguageParam = new NpgsqlParameter("@NewLanguage", NpgsqlTypes.NpgsqlDbType.Varchar, 30);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        #endregion

        #region InsertNewLnaguage // insert new language to database
        public void InsertNewLanguage(string NEW)  
        {

            try
            {
                
                NpgsqlCommand command = new NpgsqlCommand(null, dbConnection);
                command.CommandText = "INSERT INTO \"Languages\"(\"NewLanguage\")" + "VALUES(@NewLanguage)";
                NewLanguageParam.Value = NEW;

                command.Parameters.Add(NewLanguageParam);
                command.Prepare();
                command.ExecuteNonQuery();
            }
            catch (Exception ex) { MessageBox.Show(ex.ToString()); }


        }
        #endregion

        #region SaveInternToDataBase  // register intern
        public void InsertIntern(Intern intern)
        {
            try
            {

                NpgsqlCommand command = new NpgsqlCommand(null, dbConnection);
                command.CommandText = "INSERT INTO \"Intern\"(\"Id\",\"Name\",\"Surname\",\"BirthOfDay\",\"Address\",\"Email\",\"School\",\"Department\",\"Grade\",\"GPA\",\"Supervisor\",\"InternshipStarting\",\"InternshipEnding\",\"PicURL\",\"TelephoneNumber\",\"ProgrammingLanguages\",\"Comment\" )"
           + "VALUES(@Id,@Name,@Surname,@BirthOfDay,@Address,@Email,@School,@Department,@Grade,@GPA,@Supervisor,@InternshipStarting,@InternshipEnding,@PicURL,@TelephoneNumber,@ProgrammingLanguages,@Comment)";

        //Parameters
                IdParam.Value = intern.Id;
                NameParam.Value = intern.Name;
                SurnameParam.Value = intern.Surname;
                BirthOfDayParam.Value = intern.BirthOfDay;
                AddressParam.Value = intern.Address;
                EmailParam.Value = intern.Email;         
                SchoolParam.Value = intern.School;
                DepartmentParam.Value = intern.Department;
                GradeParam.Value = intern.Grade;
                GPAParam.Value = intern.GradePointAverage;
                SupervisorParam.Value = intern.Supervisor;
                InternshipStartingParam.Value = intern.InternshipStarting;
                InternshipEndingParam.Value = intern.InternshipEnding;
                PicURLParam.Value = intern.PicURL;
                TelephoneNumberParam.Value = intern.TelephoneNumber;
                ProgrammingLanguagesParam.Value = intern.ProgrammingLanguages;
                CommentParam.Value = intern.Comment;


                command.Parameters.Add(IdParam);
                command.Parameters.Add(NameParam);
                command.Parameters.Add(SurnameParam);
                command.Parameters.Add(BirthOfDayParam);
                command.Parameters.Add(AddressParam);
                command.Parameters.Add(EmailParam);
                command.Parameters.Add(SchoolParam);
                command.Parameters.Add(DepartmentParam);
                command.Parameters.Add(GradeParam);
                command.Parameters.Add(GPAParam);
                command.Parameters.Add(SupervisorParam);
                command.Parameters.Add(InternshipStartingParam);
                command.Parameters.Add(InternshipEndingParam);
                command.Parameters.Add(PicURLParam);
                command.Parameters.Add(TelephoneNumberParam);
                command.Parameters.Add(ProgrammingLanguagesParam);
                command.Parameters.Add(CommentParam);

                command.Prepare();
                command.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        #endregion // regşster Intern // register intern

        #region selectProgrammingLanguage   //This is for select all languages from database
       public List<string> SelectLanguagesFromDatabase()
        {
            List<string> results = new List<string>();
            try
            {
                NpgsqlCommand command = new NpgsqlCommand("SELECT distinct \"NewLanguage\", \"Id\" FROM \"Languages\" order by \"Id\"", dbConnection);
                NpgsqlDataReader resultset = command.ExecuteReader();
                while (resultset.Read())
                {
                    results.Add(resultset.GetString(0));
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            return results;
        }
        #endregion
        
        #region Search
        /// <summary>
        ///  Search method for SearchEntryView. Returns ObservableCollection of Interns to display it on listview.
        ///  Done by: Efe Gürkan
        /// </summary>
        /// <param name="intern"></param>
        /// <returns>ObservableCollection(Intern)</returns>
        /// 

        public ObservableCollection<Intern> Search(Intern intern)
        {
            ObservableCollection<Intern> results = new ObservableCollection<Intern>();
            //Command with no command string
            NpgsqlCommand cmd = new NpgsqlCommand(null, dbConnection);

            //Start building Command String
            string sql;

            //Default Search
            if (intern == null)
                sql = "select * from \"Intern\"";
            else //General Search
            {
                sql = "select * from \"Intern\" ";
                //To decide to use WHERE or AND
                int parameterCount = 0;

                //If Id is altered in SearchEntryView
                if (intern.Id > 0)
                {
                    //Add Where or And to the SQL
                    if (parameterCount == 0)
                        sql += "WHERE \"Id\"=";
                    if (parameterCount > 0)
                        sql += "AND \"Id\"=";

                    //Adding Parameters
                    IdParam.Value = intern.Id;
                    sql += "@Id ";

                    cmd.Parameters.Add(IdParam);
                    parameterCount++; //raised so we can decide what to put to other variables 
                }

                //Same pattern goes from now
                if (intern.Name != "" && intern.Name != null)
                {
                    if (parameterCount == 0)
                        sql += "WHERE \"Name\" ILIKE ";
                    if (parameterCount > 0)
                        sql += "AND \"Name\" ILIKE ";
                    NameParam.Value = intern.Name;
                    sql += "@Name ";

                    cmd.Parameters.Add(NameParam);
                    parameterCount++;
                }

                if (intern.Surname != "" && intern.Surname != null)
                {
                    if (parameterCount == 0)
                        sql += "WHERE \"Surname\" ILIKE ";
                    if (parameterCount > 0)
                        sql += "AND \"Surname\" ILIKE ";
                    SurnameParam.Value = intern.Surname;
                    sql += "@Surname ";

                    cmd.Parameters.Add(SurnameParam);
                    parameterCount++;
                }

                if (intern.Supervisor != "" && intern.Supervisor != null)
                {
                    if (parameterCount == 0)
                        sql += "WHERE \"Supervisor\" ILIKE ";
                    if (parameterCount > 0)
                        sql += "AND \"Supervisor\" ILIKE ";
                    SupervisorParam.Value = intern.Supervisor;
                    sql += "@Supervisor ";

                    cmd.Parameters.Add(SupervisorParam);
                    parameterCount++;
                }

                if (intern.InternshipStarting != DateTime.Today)
                {
                    if (parameterCount == 0)
                        sql += "WHERE \"InternshipStarting\"=";
                    if (parameterCount > 0)
                        sql += "AND \"InternshipStarting\"=";
                    InternshipStartingParam.Value = intern.InternshipStarting;
                    sql += "@InternshipStarting ";

                    cmd.Parameters.Add(InternshipStartingParam);
                    parameterCount++;
                }

                if (intern.School != "" && intern.School != null)
                {
                    if (parameterCount == 0)
                        sql += "WHERE \"School\" ILIKE ";
                    if (parameterCount > 0)
                        sql += "AND \"School\" ILIKE ";
                    SchoolParam.Value = intern.School;
                    sql += "@School ";

                    cmd.Parameters.Add(SchoolParam);
                    parameterCount++;
                }

                if (intern.Department != "" && intern.Department != null)
                {
                    if (parameterCount == 0)
                        sql += "WHERE \"Department\" ILIKE ";
                    if (parameterCount > 0)
                        sql += "AND \"Department\" ILIKE ";
                    DepartmentParam.Value = intern.Department;
                    sql += "@Department ";

                    cmd.Parameters.Add(DepartmentParam);
                    parameterCount++;
                }

                if (intern.GradePointAverage > 0)
                {
                    if (parameterCount == 0)
                        sql += "WHERE \"GPA\" ILIKE ";
                    if (parameterCount > 0)
                        sql += "AND  \"GPA\" ILIKE ";
                    GPAParam.Value = intern.GradePointAverage;
                    sql += "@GPA ";

                    cmd.Parameters.Add(GPAParam);
                    parameterCount++;
                }

                //Programming Languages...
                if (intern.ProgrammingLanguages != "" && intern.ProgrammingLanguages != null)
                {


                    if (intern.ProgrammingLanguages.IndexOf(';') != -1 && intern.ProgrammingLanguages.Length > 1)
                    {
                        string[] splited = intern.ProgrammingLanguages.Split(';');
                        for (int i = 0; i < splited.Length; i++)
                        {
                            if (parameterCount == 0)
                                sql += "WHERE \"ProgrammingLanguages\" ILIKE ";
                            if (parameterCount > 0)
                                sql += "AND \"ProgrammingLanguages\" ILIKE ";

                            NpgsqlParameter temp = new NpgsqlParameter("@PL" + parameterCount, NpgsqlTypes.NpgsqlDbType.Varchar, 50);
                            temp.Value = "%" + splited[i] + "%";
                            cmd.Parameters.Add(temp);
                            sql += "@PL" + parameterCount + " ";
                            parameterCount++;

                        }
                    }
                    else
                    {
                        if (parameterCount == 0)
                            sql += "WHERE \"ProgrammingLanguages\" ILIKE ";
                        if (parameterCount > 0)
                            sql += "AND \"ProgrammingLanguages\" ILIKE ";
                        NpgsqlParameter temp = new NpgsqlParameter("@PL" + parameterCount, NpgsqlTypes.NpgsqlDbType.Varchar, 50);
                        temp.Value = "%" + intern.ProgrammingLanguages + "%";
                        cmd.Parameters.Add(temp);
                        sql += "@PL" + parameterCount;
                        parameterCount++;
                    }


                }

            }
            try
            {
                //Assigning Command text to command
                cmd.CommandText = sql;
                //if it's not default search, prepare the command (it gives error if we dont check )
                if (intern != null)
                {
                    cmd.Prepare();
                }

                //Executing
                NpgsqlDataReader dr = cmd.ExecuteReader();
                //To store our data
                Intern result = new Intern();
                //While there are solutions...
                while (dr.Read())
                {
                    //Generating Intern
                    result.Id = (long)dr.GetInt64(dr.GetOrdinal("Id"));
                    //Null checks starts from now
                    if (!dr.IsDBNull(dr.GetOrdinal("Name")))
                        result.Name = dr.GetString(dr.GetOrdinal("Name"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Name")))
                        result.Surname = dr.GetString(dr.GetOrdinal("Surname"));

                    if (!dr.IsDBNull(dr.GetOrdinal("BirthOfDay")))
                        result.BirthOfDay = dr.GetDateTime(dr.GetOrdinal("BirthOfDay"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Address")))
                        result.Address = dr.GetString(dr.GetOrdinal("Address"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Email")))
                        result.Email = dr.GetString(dr.GetOrdinal("Email"));

                    if (!dr.IsDBNull(dr.GetOrdinal("School")))
                        result.School = dr.GetString(dr.GetOrdinal("School"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Department")))
                        result.Department = dr.GetString(dr.GetOrdinal("Department"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Grade")))
                        result.Grade = dr.GetString(dr.GetOrdinal("Grade"));

                    if (!dr.IsDBNull(dr.GetOrdinal("GPA")))
                        result.GradePointAverage = dr.GetDouble(dr.GetOrdinal("GPA"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Supervisor")))
                        result.Supervisor = dr.GetString(dr.GetOrdinal("Supervisor"));

                    if (!dr.IsDBNull(dr.GetOrdinal("InternshipStarting")))
                        result.InternshipStarting = dr.GetDateTime(dr.GetOrdinal("InternshipStarting"));

                    if (!dr.IsDBNull(dr.GetOrdinal("InternshipEnding")))
                        result.InternshipEnding = dr.GetDateTime(dr.GetOrdinal("InternshipEnding"));

                    if (!dr.IsDBNull(dr.GetOrdinal("PicURL")))
                        result.PicURL = dr.GetString(dr.GetOrdinal("PicURL"));

                    if (!dr.IsDBNull(dr.GetOrdinal("TelephoneNumber")))
                        result.TelephoneNumber = dr.GetString(dr.GetOrdinal("TelephoneNumber"));

                    if (!dr.IsDBNull(dr.GetOrdinal("ProgrammingLanguages")))
                        result.ProgrammingLanguages = dr.GetString(dr.GetOrdinal("ProgrammingLanguages"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Comment")))
                        result.Comment = dr.GetString(dr.GetOrdinal("Comment"));

                    //Adding generated Intern to our observablecollection(results)
                    results.Add(result);
                    result = new Intern();

                }
                dr.Close();
                return results;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return null;
            }


        }
        #endregion

        #region EditINTERN  // Update Intern
        public void EditIntern(Intern intern, long specificId)
        {

            try
            {
                NpgsqlCommand EditCommand = new NpgsqlCommand(null, dbConnection);

                EditCommand.CommandText = "UPDATE \"Intern\" SET \"Id\"=@Id, \"Name\"=@Name,\"Surname\"=@Surname,\"BirthOfDay\"=@BirthOfDay,\"Address\"=@Address,\"Email\"=@Email,\"School\"=@School,\"Department\"=@Department,\"Grade\"=@Grade,\"GPA\"=@GPA,\"Supervisor\"=@Supervisor,\"InternshipStarting\"=@InternshipStarting,\"InternshipEnding\"=@InternshipEnding,\"PicURL\"=@PicURL,\"TelephoneNumber\"=@TelephoneNumber,\"ProgrammingLanguages\"=@ProgrammingLanguages,\"Comment\"=@Comment"
                    + " WHERE \"Id\"= @InternID; ";

             
                NpgsqlParameter InternIdParam = new NpgsqlParameter("@InternID", NpgsqlTypes.NpgsqlDbType.Bigint);
                InternIdParam.Value = specificId;
                EditCommand.Parameters.Add(InternIdParam);
                IdParam.Value = intern.Id;
                NameParam.Value = intern.Name;
                SurnameParam.Value = intern.Surname;
                BirthOfDayParam.Value = intern.BirthOfDay;
                AddressParam.Value = intern.Address;
                EmailParam.Value = intern.Email;
                SchoolParam.Value = intern.School;
                DepartmentParam.Value = intern.Department;
                GradeParam.Value = intern.Grade;
                GPAParam.Value = intern.GradePointAverage;
                SupervisorParam.Value = intern.Supervisor;
                InternshipStartingParam.Value = intern.InternshipStarting;
                InternshipEndingParam.Value = intern.InternshipEnding;
                PicURLParam.Value = intern.PicURL;
                TelephoneNumberParam.Value = intern.TelephoneNumber;
                ProgrammingLanguagesParam.Value = intern.ProgrammingLanguages;
                CommentParam.Value = intern.Comment;


                EditCommand.Parameters.Add(IdParam);
                EditCommand.Parameters.Add(NameParam);
                EditCommand.Parameters.Add(SurnameParam);
                EditCommand.Parameters.Add(BirthOfDayParam);
                EditCommand.Parameters.Add(AddressParam);
                EditCommand.Parameters.Add(EmailParam);
                EditCommand.Parameters.Add(SchoolParam);
                EditCommand.Parameters.Add(DepartmentParam);
                EditCommand.Parameters.Add(GradeParam);
                EditCommand.Parameters.Add(GPAParam);
                EditCommand.Parameters.Add(SupervisorParam);
                EditCommand.Parameters.Add(InternshipStartingParam);
                EditCommand.Parameters.Add(InternshipEndingParam);
                EditCommand.Parameters.Add(PicURLParam);
                EditCommand.Parameters.Add(TelephoneNumberParam);
                EditCommand.Parameters.Add(ProgrammingLanguagesParam);
                EditCommand.Parameters.Add(CommentParam);



                EditCommand.Prepare();
                EditCommand.ExecuteNonQuery();
            }


            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }


        }

        #endregion

        #region DeleteIntern   // DeleteIntern from database
        public void DeleteIntern(Intern DeletedIntern, long UniqueID)
        {
            try
            {
                NpgsqlCommand DeleteCommand = new NpgsqlCommand(null, dbConnection);

                DeleteCommand.CommandText = "DELETE FROM \"Intern\"   WHERE \"Id\"= @InternID; ";
                NpgsqlParameter InternIDParam = new NpgsqlParameter("@InternID", NpgsqlTypes.NpgsqlDbType.Bigint);
                InternIDParam.Value = UniqueID;
                DeleteCommand.Parameters.Add(InternIDParam);
                DeleteCommand.Prepare();
                DeleteCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            { MessageBox.Show(ex.ToString()); }




        }

        #endregion

        #region Release  //If Connection is opened, close.
        public void Release()
        {
            
            //Used at SearchEntryViewModel while exiting the progeram
            if (dbConnection.State == System.Data.ConnectionState.Open)
            {

                dbConnection.Close();
            }
        }


        #endregion



    }
}
